package educrypt.database.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Observable;

import educrypt.commons.Alphabet;
import educrypt.database.Connector;
import educrypt.exceptions.EduCryptException;
import educrypt.i18n.Messages;

public class AlphabetDAO extends Observable {

	/* Singleton */
	private AlphabetDAO() {
	}

	public static AlphabetDAO getInstance() {
		return SingletonHolder.instance;
	}

	private static class SingletonHolder {
		public static final AlphabetDAO instance = new AlphabetDAO();
	}

	/* END Singleton */

	public Alphabet find(final String name) {
		String sqlFindAlphabet = "SELECT * FROM Alphabet WHERE name='" + name
				+ "'";
		Alphabet alphabet = null;
		try {
			Connection conn = Connector.getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sqlFindAlphabet);
			String symbols = "";
			if (rs.next()) {
				symbols = rs.getString("symbols");
				alphabet = new Alphabet(name, symbols);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return alphabet;
	}

	public List<Alphabet> findByCiphers(List<Integer> ciphers) {
		String sqlSubQuery = "SELECT alphabetName FROM Alphabet_Cipher WHERE a.name=alphabetName AND (";
		int numberOfCompatibleCiphers = ciphers.size();
		for (int i = 0; i < numberOfCompatibleCiphers; i++) {
			if (i == numberOfCompatibleCiphers - 1) {
				sqlSubQuery += "cipher=" + ciphers.get(i);
			} else {
				sqlSubQuery += "cipher=" + ciphers.get(i) + " OR ";
			}
		}
		// OR cipher=99 <- WORKAROUND to avoid strange sql error
		sqlSubQuery += "OR cipher=99) having count(alphabetName)="
				+ numberOfCompatibleCiphers;

		String sqlFindAlphabetByCipher = "SELECT * FROM Alphabet a WHERE EXISTS "
				+ "(" + sqlSubQuery + ")";

		List<Alphabet> alphabets = new ArrayList<Alphabet>();
		try {
			Connection conn = Connector.getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sqlFindAlphabetByCipher);
			String name = "";
			String symbols = "";
			while (rs.next()) {
				name = rs.getString("name");
				symbols = rs.getString("symbols");
				alphabets.add(new Alphabet(name, symbols));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return alphabets;
	}

	public List<Alphabet> findByCipher(int cipher) {
		String sqlFindAlphabetByCipher = "SELECT * FROM Alphabet a WHERE EXISTS "
				+ "(SELECT alphabetName FROM Alphabet_Cipher WHERE a.name=alphabetName AND cipher="
				+ cipher + ")";
		List<Alphabet> alphabets = new ArrayList<Alphabet>();
		try {
			Connection conn = Connector.getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sqlFindAlphabetByCipher);
			String name = "";
			String symbols = "";
			while (rs.next()) {
				name = rs.getString("name");
				symbols = rs.getString("symbols");
				alphabets.add(new Alphabet(name, symbols));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return alphabets;
	}

	public List<Alphabet> findAll() {
		String sqlFindAllAlphabets = "SELECT * FROM Alphabet";
		List<Alphabet> allAlphabets = new ArrayList<Alphabet>();
		try {
			Connection conn = Connector.getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sqlFindAllAlphabets);
			String name = "";
			String symbols = "";
			while (rs.next()) {
				name = rs.getString("name");
				symbols = rs.getString("symbols");
				allAlphabets.add(new Alphabet(name, symbols));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return allAlphabets;
	}

	public boolean exists(Alphabet alphabet) {
		final String sqlExistsAlphabet = "SELECT COUNT(*) from Alphabet WHERE name=?";
		boolean exists = false;
		try {
			Connection conn = Connector.getConnection();
			PreparedStatement ps = conn.prepareStatement(sqlExistsAlphabet);
			ps.setString(1, alphabet.getName());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				exists = rs.getInt(1) > 0;
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return exists;
	}

	public void create(Alphabet alphabet, List<Integer> ciphersCompatibility)
			throws EduCryptException {
		if (!exists(alphabet)) {
			String sqlCreateAlphabet = "INSERT INTO Alphabet (name, symbols) VALUES(?,?)";
			try {
				Connection conn = Connector.getConnection();
				PreparedStatement ps = conn.prepareStatement(sqlCreateAlphabet);
				ps.setString(1, alphabet.getName());
				ps.setString(2, alphabet.getSymbols());
				ps.executeUpdate();
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			createCompatibility(ciphersCompatibility, alphabet.getName());

			this.setChanged();
			this.notifyObservers();
		} else {
			throw new EduCryptException(
					Messages.getString("errAlphabetAlreadyExists"));
		}
	}

	private void createCompatibility(List<Integer> ciphers, String alphabetName) {
		String sqlCreateCipherCompatibility = "INSERT INTO Alphabet_Cipher (alphabetName, cipher) VALUES(?,?)";
		try {
			Connection conn = Connector.getConnection();
			PreparedStatement ps = conn
					.prepareStatement(sqlCreateCipherCompatibility);
			for (int cipher : ciphers) {
				ps.setString(1, alphabetName);
				ps.setInt(2, cipher);
				ps.addBatch();
			}
			ps.executeBatch();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void delete(String name) {
		// All keywords and analysis related to the alphabet are deleted too
		String sqlDeleteAlphabet = "DELETE Alphabet WHERE name='" + name + "'";
		try {
			Connection conn = Connector.getConnection();
			Statement st = conn.createStatement();
			st.executeUpdate(sqlDeleteAlphabet);
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		this.setChanged();
		this.notifyObservers();
	}
}
